﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using DevMaker.SIC.Model;
using log4net;


namespace DevMaker.SIC.Dao {
    public class TurnoDao {
        private static readonly ILog Log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public Turno Crear(Turno turno) {
            const string storeName = "sp_turnos_c";
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIC_Connection"].ToString());
            SqlTransaction trans = null;
            try {
                conn.Open();
                var command = new SqlCommand(storeName, conn) { Transaction = conn.BeginTransaction(), CommandType = CommandType.StoredProcedure };
                trans = command.Transaction;
                command.Parameters.AddWithValue("@fecha_inicio", turno.FechaInicio);
                if (turno.FechaCierre.HasValue)
                    command.Parameters.AddWithValue("@fecha_termino", turno.FechaCierre);
                else
                    command.Parameters.AddWithValue("@fecha_termino", DBNull.Value);
                command.Parameters.AddWithValue("@id_sucursal", turno.Sucursal.Id);
                command.Parameters.AddWithValue("@cod_sucursal", turno.Sucursal.Codigo);
                command.Parameters.AddWithValue("@id_punto_venta", turno.PuntoVenta.Code);
                command.Parameters.AddWithValue("@id_cajero", turno.Cajero.Id);
                command.Parameters.AddWithValue("@id_supervisor_apertura", turno.SupervisorApertura.Id);
                command.Parameters.AddWithValue("@id_supervisor_cierre", DBNull.Value);
                command.Parameters.AddWithValue("@id_estado", turno.EstadoTurno);

                var outParam = command.Parameters.Add("@id", SqlDbType.BigInt);
                outParam.Direction = ParameterDirection.Output;
                command.ExecuteScalar();
                turno.Id = Convert.ToInt32(outParam.Value);

                var reposicion = new ReposicionDao().Crear(turno, turno.Reposiciones[0], command.Transaction);
                command.Transaction.Commit();
            } catch (Exception ex) {
                Log.Error(ex);
                turno.Id = 0;
                if (trans != null)
                    trans.Rollback();
                throw;
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return turno;
        }

        public List<Turno> ObtieneTurnosAbiertos() {
            const string storeName = "sp_turnos_abiertos_l";
            var list = new List<Turno>();
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIC_Connection"].ToString());
            try {
                conn.Open();
                var command = new SqlCommand(storeName, conn) { CommandType = CommandType.StoredProcedure };
                var reader = command.ExecuteReader();
                while (reader.Read()) {
                    var p = new Turno();
                    p.ReadFromDataReader(reader);
                    list.Add(p);
                }
            } catch (Exception ex) {
                Log.Error(ex);
                throw;
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return list;
        }

        public Turno ObtieneTurnoPorId(Int32 id) {
            var turno = new Turno();
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIC_Connection"].ToString());
            try {
                conn.Open();
                var command = new SqlCommand("sp_turnos_por_id_l", conn) { CommandType = CommandType.StoredProcedure };
                command.Parameters.AddWithValue("@id", id);
                var reader = command.ExecuteReader();
                while (reader.Read()) {
                    turno.ReadFromDataReader(reader);
                    turno.Reposiciones = new ReposicionDao().ObtienePorTurno(turno);
                    turno.Cierre = new CierreDao().ObtienePorTurno(turno);
                }
            } catch (Exception ex) {
                Log.Error(ex);
                throw;
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return turno;
        }

        public Turno CrearCierre(Turno turno) {
            const string storeName = "sp_turnos_cierre_u";
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIC_Connection"].ToString());
            SqlTransaction trans = null;
            try {
                conn.Open();
                var command = new SqlCommand(storeName, conn) { Transaction = conn.BeginTransaction(), CommandType = CommandType.StoredProcedure };
                trans = command.Transaction;
                command.Parameters.AddWithValue("@id", turno.Id);
                if (turno.FechaCierre.HasValue)
                    command.Parameters.AddWithValue("@fecha_termino", turno.FechaCierre);
                else
                    command.Parameters.AddWithValue("@fecha_termino", DBNull.Value);
                command.Parameters.AddWithValue("@id_supervisor_cierre", turno.SupervisorCierre.Id);
                command.ExecuteNonQuery();

                var cierre = new CierreDao().Crear(turno, turno.Cierre, command.Transaction);
                command.Transaction.Commit();
            } catch (Exception ex) {
                Log.Error(ex);
                turno.Id = 0;
                if (trans != null)
                    trans.Rollback();
                throw;
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return turno;
        }

        public List<Turno> ObtieneTurnosCerrados() {
            const string storeName = "sp_turnos_cerrados_l";
            var list = new List<Turno>();
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIC_Connection"].ToString());
            try {
                conn.Open();
                var command = new SqlCommand(storeName, conn) { CommandType = CommandType.StoredProcedure };
                var reader = command.ExecuteReader();
                while (reader.Read()) {
                    var p = new Turno();
                    p.ReadFromDataReader(reader);
                    p.Cierre = new Cierre();
                    p.Cierre.ReadFromDataReader(reader);
                    list.Add(p);
                }
            } catch (Exception ex) {
                Log.Error(ex);
                throw;
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return list;

        }

        public List<Turno> ObtieneUltimosTurnosCerrados() {
            const string storeName = "sp_turnos_cerrados_ultimos_l";
            var list = new List<Turno>();
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIC_Connection"].ToString());
            try {
                conn.Open();
                var command = new SqlCommand(storeName, conn) { CommandType = CommandType.StoredProcedure };
                var reader = command.ExecuteReader();
                while (reader.Read()) {
                    var p = new Turno();
                    p.ReadFromDataReader(reader);
                    p.Cierre = new Cierre();
                    p.Cierre.ReadFromDataReader(reader);
                    list.Add(p);
                }
            } catch (Exception ex) {
                Log.Error(ex);
                throw;
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return list;
        }

        public List<Turno> ObtieneTurnosCerradosPorFecha(DateTime fechaInicio, DateTime fechaFin) {
            const string storeName = "sp_turnos_cerrados_por_fecha_l";
            var list = new List<Turno>();
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIC_Connection"].ToString());
            try {
                conn.Open();
                var command = new SqlCommand(storeName, conn) { CommandType = CommandType.StoredProcedure };
                command.Parameters.AddWithValue("@fecha_inicio", fechaInicio);
                command.Parameters.AddWithValue("@fecha_termino", fechaFin);
                var reader = command.ExecuteReader();
                while (reader.Read()) {
                    var p = new Turno();
                    p.ReadFromDataReader(reader);
                    p.Cierre = new Cierre();
                    p.Cierre.ReadFromDataReader(reader);
                    list.Add(p);
                }
            } catch (Exception ex) {
                Log.Error(ex);
                throw;
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return list;
        }
    }
}